package ca.ualberta.cs.CLRDB;

import java.io.IOException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;

import ca.ualberta.cs.CLRDB.conference.AnnotationMgmtConference;
import ca.ualberta.cs.CLRDB.conference.ConferenceController;
import ca.ualberta.cs.CLRDB.conference.PersonsMgmtConference;
import ca.ualberta.cs.CLRDB.conference.ResourceMgmtConference;
import ca.ualberta.cs.CLRDB.conference.RolesManagement;
import ca.ualberta.cs.CLRDB.interfaces.AnnotationAccess;
import ca.ualberta.cs.CLRDB.interfaces.EntityRelAccess;
import ca.ualberta.cs.CLRDB.interfaces.EntityRelManagement;
import ca.ualberta.cs.CLRDB.interfaces.PersonAccess;
import ca.ualberta.cs.CLRDB.interfaces.RateAccess;
import ca.ualberta.cs.CLRDB.interfaces.ResourceAccess;
import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;
import ca.ualberta.cs.PrivacyManagement.Settings;

public class DatabaseController {
	
private static String urlPrefix = "jdbc:db2:";
    

	private static String url = "";	
   
   private static String username = "";
   private static String password = "";
   private static String databaseName = "";

   public static PersonAccess personsMgmt = new PersonsManagement();
   public static ResourceAccess resourceMgmt = new ResourceManagement();
   public static RateAccess rateMgmt = new RateManagement();
   public static AnnotationAccess annoMgmt = new AnnotationDatabase();
   
   public static TextSnapshotsManagement textsnapMgmt = new TextSnapshotsManagement();
   
   public static ConferenceController conferenceMgmt = new ConferenceController();
   public static Settings settingsMgmt = new Settings();
   
   //String: mainTableName
   public static HashMap<String,EntityRelAccess> mgmts = new HashMap<String, EntityRelAccess>();
   
   public DatabaseController(String urll, String user, String pass){
	   setUrl(urll);
	   setUsername(user);
	   setPassword(pass);
	   
	   initMgmts();
   }
      
   private void initMgmts(){
	   mgmts.put("Person", personsMgmt);
	   mgmts.put("Resource", resourceMgmt);
	   mgmts.put("RateResource", rateMgmt);
	   mgmts.put("Annotation", annoMgmt);
	   mgmts.put("TextSnap", textsnapMgmt);
	   //mgmts.put("Statistics", statMgmt);
	   
	   mgmts.put("conference", conferenceMgmt);
	   mgmts.put("settings", settingsMgmt);
   }   
	  
	  
   private boolean createDatabase(){
	   Connection con = null;
	   try{
		   con = connectToSampleDB();
		   Statement st = con.createStatement();
		   st.executeUpdate("CREATE DATABASE "+databaseName+" " +
		   		"AUTOMATIC STORAGE YES " +
		   		"USING CODESET UTF-8 TERRITORY CA COLLATE USING SYSTEM");
	   }catch(SQLException e){
		   if( con == null ){
			   System.out.println("Could not connect to SAMPLE database to create your " +
			   		"requested database ("+url+"). Try creating it yourself!!");			   
		   }
		   else
			   DB2SqlException.printSqlErrorMsg(e);
		   return false;
	   }
	   return true;
   }
   
   public boolean initializeDB(){

	   /*if( !createDatabase() )
		   return false;*/
	   
	   Connection con = null ;    	
	   	con = connectToDB();
	   	Statement statement = null;
	   		   	
	   	if( con == null ) {//in case of exception
	   		System.out.println("Failed to connect to DB, none of DB queries will be handled from now. Try again.");
	   		return false;
	   	}
	   			
		
		try {
			//dropping all tables
			statement = con.createStatement();
   		
   		try{
				statement.executeUpdate("DROP TABLESPACE CLRSPACE");
				/*statement.close();
				con.commit();*/
   		}
   		catch(Exception e){
   			System.out.println("I guess there is no tablespace named CLRSPACE to be dropped");
   			System.err.println ("Error msg: " + e.getMessage());
   		}
			try{	
				statement.executeUpdate("DROP BUFFERPOOL CLRBuf");
   		}
   		catch(Exception e){
   			System.out.println("I guess there is no bufferpool named CLRBuf to be dropped");
   			System.err.println ("Error msg: " + e.getMessage());
   		}
			
			//creating fresh tables for CLR
   		statement.executeUpdate("CREATE BUFFERPOOL CLRBuf " +
   				"IMMEDIATE "+
   				"SIZE 5000 " +
   				"PAGESIZE 32K");
   	
			statement.executeUpdate("CREATE TABLESPACE CLRSPACE " +
					"PAGESIZE 32K " + //TODO
					"AUTORESIZE YES " +
					"BUFFERPOOL CLRBuf" 
					);					
						
			personsMgmt.initialize(statement);
			
			personsMgmt.getFriendsManagement().initialize(statement);
			
			resourceMgmt.initialize(statement);
			
			annoMgmt.initialize(statement);
			
			rateMgmt.initialize(statement);
			
			resourceMgmt.getAuthoringManagement().initialize(statement);
			
			resourceMgmt.getTagsManagement().initialize(statement);
			
			resourceMgmt.getReadingManagement().initialize(statement);
			
			//addTypeAndRateToAnnoTable(con);
			
			textsnapMgmt.initialize(statement);
			
	   		statement.executeUpdate("ALTER TABLE Annotation " +
	   				"ADD COLUMN " +
	   					"textsnapshot INTEGER " +	   				
	   				"ADD CONSTRAINT FK_textid FOREIGN KEY (textsnapshot) " +
                	"REFERENCES TextSnapshots (ID) ON DELETE CASCADE " +
	   				"");


	   		settingsMgmt.initialize(statement);
	   		
	   		if(databaseName.equalsIgnoreCase("CONFER")){
		   		conferenceMgmt.initialize(statement);		  
	   		}
	   		
//	   		addAbstractToBibtexTable();

//	   		changeTagsFromClobToVarchar();
			
			/*try{
			statement.executeUpdate("DROP TRIGGER annoHasReply");
		}
   		catch(Exception e){
   			System.out.println("I guess there is no bufferpool named CLRBuf to be dropped");
   			System.err.println ("Error msg: " + e.getMessage());
   		}
		
		statement.close();
		con.commit();
		statement = con.createStatement();
		*/
		
		statement.executeUpdate("CREATE TRIGGER annoHasReply AFTER INSERT ON Annotation " +
				"REFERENCING NEW AS n " +
				"FOR EACH ROW " +
				"WHEN (n.replyTo IS NOT NULL) " +
				"UPDATE Annotation SET hasReply = 'y' WHERE ID = n.replyTo");
				
				/*DROP TRIGGER annoHasReply;*/

		/*CREATE TRIGGER annoHasReply
AFTER INSERT ON DB2ADMIN.ANNOTATION 
REFERENCING NEW AS N 
WHEN (N.REPLYTO != NULL) 
UPDATE DB2ADMIN.ANNOTATION SET HASREPLY = 'y' WHERE ID = N.REPLYTO;
		*/		
				
				
		statement.executeUpdate("CREATE TRIGGER DELREPLY AFTER DELETE  " +
				"ON Annotation REFERENCING OLD AS OL " +
				"FOR EACH ROW  MODE DB2SQL " + 
				"WHEN ( OL.replyTo IS NOT NULL ) "+
				"BEGIN ATOMIC " + 
				"DECLARE x INT; " +
				"SET x = (SELECT COUNT(*) FROM Annotation WHERE replyTo = OL.replyTo AND ID != OL.ID); " +
				"IF x = 0 THEN " +
				"UPDATE Annotation SET hasreply = 'n' WHERE ID = OL.replyTo; " +
				"END IF; " +
				"END");
				
		
		addTriggerForBibtexid(statement);
		
		statement.close();
		con.commit();					
		
		
	} catch (SQLException ex) {
		DB2SqlException.printSqlErrorMsg(ex);		
	}
	
	return true;

   }
   
   public void addKeywords(){
	   Connection con = connectToDB();
	   Statement statement = null;
	   	
	   	try {			
	   		statement = con.createStatement();
	   		
	   		try{		   		
		   		statement.executeUpdate("DROP TABLE keywords");
	   		}catch(Exception e){
	   			System.out.println("no table named keywords.");
	   		}
	   		
	   		
	   		statement.executeUpdate("CREATE TABLE Keywords ( " +				
					"label VARCHAR(100) NOT NULL, " +				
					"bibtexId INTEGER NOT NULL, " +				
					"CONSTRAINT FK_bibtexid FOREIGN KEY (bibtexId) " +
	                	"REFERENCES Bibtex (ID) ON DELETE CASCADE, " +                
	                "CONSTRAINT PK_keywords PRIMARY KEY (bibtexId, label) " +     
			") IN CLRSPACE");
	   		
	   		
	   		statement.close();
		   
		   con.commit();
		   con.close();				   
		   				
		
		} catch (SQLException ex) {
			DB2SqlException.printSqlErrorMsg(ex);			
		}
	
   }
   
public void addTagCount(){
	   
	   Connection con = connectToDB();
	   Statement statement = null;
	   	
	   	try {			
	   		statement = con.createStatement();
	   		
	   		try{		
	   			statement.executeUpdate("DROP TABLE TagCount");
		   		statement.executeUpdate("DROP TRIGGER tagCountUpdate");
	   		}catch(Exception e){
	   			System.out.println("no trigger/table named tagCountUpdate/tagcount");
	   		}
	   		
	   		statement.executeUpdate("CREATE TABLE TagCount ( " +
					"tagId INTEGER NOT NULL, " +
					"label VARCHAR(100) NOT NULL, " +				
					"resourceId INTEGER NOT NULL, " +
					"num INTEGER NOT NULL DEFAULT 0, " +				
					"CONSTRAINT FK_resourceid FOREIGN KEY (resourceId) " +
	                	"REFERENCES Resource (ID) ON DELETE CASCADE, " +                
	                "CONSTRAINT FK_tagid FOREIGN KEY (tagId) " +
	                	"REFERENCES Tag (ID) ON DELETE CASCADE, " +
	                "CONSTRAINT PK_tagging PRIMARY KEY (resourceId, label) " +     
			") IN CLRSPACE");
	   		
	   		statement.executeUpdate("CREATE TRIGGER tagCountUpdate AFTER INSERT ON Tagging " +
	   				"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"BEGIN ATOMIC " + 
					"DECLARE x INT; " +
					"SET x = (SELECT COUNT(*) FROM TagCount WHERE label = n.label AND resourceid = n.resourceid ); " +
					"IF x = 0 THEN " +
					"INSERT INTO TagCount (tagId,label,resourceId,num) " +
						"VALUES (n.tagId,n.label,n.resourceId,1) ;" +
					"ELSE " +
					"UPDATE TagCount SET num = num+1 WHERE  label = n.label AND resourceid = n.resourceid ;" +
					"END IF; " +
					"END");
			
	   		
	   		
	   		statement.close();
		   
		   con.commit();
		   con.close();				   
		   				
		
		} catch (SQLException ex) {
			DB2SqlException.printSqlErrorMsg(ex);		
		}
	   
   }
   
public void addTriggerForBibTexid2_bibNull(Statement statement){ //called in addTriggerForBibTexid which is called in initialize
	   
	   Connection con = null;
	   boolean innerCon = false;
	   	
	   	try {			
	   		if(statement == null ){
		   		   con = connectToDB();	   		
		   		   statement = con.createStatement();
		   		   innerCon = true;
	   		}	   		
	   		
	   		try{		   		
		   		statement.executeUpdate("DROP TRIGGER sameDigestBibNullUpdate");
	   		}catch(Exception e){
	   			System.out.println("no trigger named sameDigestBibNullUpdate");
	   		}
	   		
	   		
	   		statement.executeUpdate("CREATE TRIGGER sameDigestBibNullUpdate AFTER Update OF documentdigest ON Resource " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"WHEN ( n.bibtexid IS NULL AND n.documentdigest IS NOT NULL)"+
					"BEGIN ATOMIC " + 
					"DECLARE x INT; " +
					"SET x = (SELECT DISTINCT bibtexid FROM Resource WHERE documentdigest = n.documentdigest AND ID != n.ID " +
						"AND bibtexid IS NOT NULL " +
							"FETCH FIRST 1 ROWS ONLY); " +
					"IF x IS NOT NULL THEN " +
					"UPDATE Resource SET bibtexid = x WHERE id = n.id;" +
					"END IF; " +
					"END");
			
	   		
	   		if(innerCon){
		   	   statement.close();			   
			   con.commit();
			   con.close();				   
	   		}				
		
		} catch (SQLException ex) {
			DB2SqlException.printSqlErrorMsg(ex);			
		}
	   
}

public void addTriggerForBibtexid(Statement statement){ //called within initializeDB
	   
	boolean innerCon = false;   
	Connection con = null;
	   	try {
	   		if(statement == null ){
	   		   con = connectToDB();	   		
	   		   statement = con.createStatement();
	   		   innerCon = true;
	   		}
	   		
	   		try{
	   			statement.executeUpdate("DROP TRIGGER sameDigestBibNotNullInsert");	   		
	   		}catch(Exception e){
	   			System.out.println("no trigger named sameDigestBibNotNullInsert");
	   		}
	   		try{		   		
		   		statement.executeUpdate("DROP TRIGGER sameDigestBibNotNullUpdate");		   		
	   		}catch(Exception e){
	   			System.out.println("no trigger named sameDigestBibNotNullUpdate");
	   		}
	   		try{		   		
		   		statement.executeUpdate("DROP TRIGGER sameDigestBibNull");
	   		}catch(Exception e){
	   			System.out.println("no trigger named sameDigestBibNull");
	   		}
	   		
	   		statement.executeUpdate("CREATE TRIGGER sameDigestBibNotNullInsert AFTER INSERT ON Resource " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"WHEN ( n.bibtexid IS NOT NULL AND n.documentdigest IS NOT NULL) "+
					"UPDATE Resource SET bibtexid = n.bibtexid WHERE documentdigest = n.documentdigest AND bibtexid IS NULL ");					
			
	   		statement.executeUpdate("CREATE TRIGGER sameDigestBibNotNullUpdate AFTER Update OF bibtexid, documentdigest ON Resource " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"WHEN ( n.bibtexid IS NOT NULL AND n.documentdigest IS NOT NULL) "+
					"UPDATE Resource SET bibtexid = n.bibtexid WHERE documentdigest = n.documentdigest AND bibtexid IS NULL ");					
						   		
	   		/*statement.executeUpdate("CREATE TRIGGER sameDigestBibNullUpdate AFTER Update OF documentdigest ON Resource " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"WHEN ( n.bibtexid IS NULL AND n.documentdigest IS NOT NULL) "+
					s"UPDATE Resource SET n.bibtexid = n.bibtexid WHERE documentdigest = n.documentdigest AND bibtexid IS NULL ");*/
	   		
	   		statement.executeUpdate("CREATE TRIGGER sameDigestBibNull AFTER INSERT ON Resource " +
					"REFERENCING NEW AS n " +
					"FOR EACH ROW  MODE DB2SQL " + 
					"WHEN ( n.bibtexid IS NULL AND n.documentdigest IS NOT NULL) "+
					"BEGIN ATOMIC " + 
					"DECLARE x INT; " +
					"SET x = (SELECT DISTINCT bibtexid FROM Resource WHERE documentdigest = n.documentdigest AND ID != n.ID " +
						"AND bibtexid IS NOT NULL " +
							"FETCH FIRST 1 ROWS ONLY); " +
					"IF x IS NOT NULL THEN " +
					"UPDATE Resource SET bibtexid = x WHERE ID = n.ID; " +
					"END IF; " +
					"END");
	   		
	   		if(innerCon){
	   			statement.close();			   
			   con.commit();
			   con.close();	
	   		}
		   	
		   addTriggerForBibTexid2_bibNull(statement);
		
		} catch (SQLException ex) {
			DB2SqlException.printSqlErrorMsg(ex);		
		}
}


   public static void setUrl(String url) {
		DatabaseController.url = url;
	}
	
	public static void setUsername(String username) {
		DatabaseController.username = username;
	}
	
	public static void setPassword(String password) {
		DatabaseController.password = password;
	}

	public static void setDatabaseName(String name){
		DatabaseController.databaseName = name;
	}
	
	//investigate: should have one connection for all, or should have one connection per action?
	   public static Connection connectToDB(){
	   	Connection con = null;
	   	try 
	       {                                                                        
	         // Load JDBC Driver
	         Class.forName("com.ibm.db2.jcc.DB2Driver");                                        

	         con = DriverManager.getConnection (url,username,password);                                           
	         con.setAutoCommit(false);
	       }
	   	catch (ClassNotFoundException e)
	   	{
	   		System.err.println("Could not load JDBC driver");
				System.out.println("Exception: " + e);
				    e.printStackTrace();
			}
			catch(SQLException ex)                                                      
			{
				DB2SqlException.printSqlErrorMsg(ex);
			}
			return con;
	   }
	   
	   private Connection connectToSampleDB() throws SQLException{
		   	Connection con = null;
		   	try 
		       {                                                                        
		         // Load JDBC Driver
		         Class.forName("com.ibm.db2.jcc.DB2Driver");                                        

		         String sampleUrl = url.substring(0,url.lastIndexOf("/")) + "SAMPLE";
		         con = DriverManager.getConnection (sampleUrl,username,password);                                           
		         con.setAutoCommit(false);
		       }
		   	catch (ClassNotFoundException e){
		   		System.err.println("Could not load JDBC driver");
					System.out.println("Exception: " + e);
					    e.printStackTrace();
			}
			catch(SQLException ex){
					throw ex;
			}
			return con;
	   }


	   public static void main(String[] args) throws IOException{
			
			//args[0]: which db? "samaneh", "hypatia"
			//args[1]: what to do? "recreate", "print", "call"
			//args[2]: if \"recreate\", which table? tablename
			//		   if "print", which table? tablename
			//		   if "call", what method? methodname
			
			Class c = DatabaseController.class;
			Method[] methods = c.getMethods();
			
			if(args.length < 4){
				System.out.println("re-run with arguments:");
				System.out.println("args[0]: url of db " +
						//"\"S\" for samaneh's local machine, \"H\" for hypatia, \"E\" for hypatia-first experiment \n" +
						"args[1]: username (db) \n" +
						"args[2]: password (db) \n" +
						"args[3]: what to do? \"recreate\", \"print\", \"call\" \n" +
						"args[4]: if \"recreate\", which table? tablename, or the all?: 'database' \n" +
						"		  if \"print\", which table? tablename \n" +
						"         if \"call\", what method? method-code ");
				System.out.println();
				System.out.println("method-code: ");
				for(int i = 1 ; i <= methods.length ; i++ )
					System.out.println(i+"   :  "+methods[i-1].getName());
				
				return;
			}		
			
			/*char d = 's';
			if(args[0].equalsIgnoreCase("S")){ //running from my local machine			
			}
			else if(args[0].equalsIgnoreCase("H")){ //running from my local machine
				d = 'H';
			}
			else if(args[0].equalsIgnoreCase("E")){ //running from my local machine
				d = 'E';
			}
			
			DatabaseController db = new DatabaseController(d);*/
			
			DatabaseController db = new DatabaseController(args[0], args[1], args[2]);
			
			if(args[3].equalsIgnoreCase("call")){
				try{
					int code = Integer.parseInt(args[4]);
					methods[code-1].invoke(db);
				}catch(Exception e){
					System.out.println("method code does not exist, or");
					System.out.println("this method is not supported - needs input arguments");
				}
			}
			else{
				String tableName = null;
				if(!args[4].equalsIgnoreCase("database")){										
					tableName = args[4];
				}
				if(args[3].equalsIgnoreCase("recreate")){								
			
					if(args[4].equalsIgnoreCase("database")){
						System.out.println("Press Enter if you are sure you want to clear and rebuild the CLR database");
						  System.out.println("otherwise, kill this program by yourself!!");			  
						  System.in.read();
						db.initializeDB();
					}
					else{
						if(!mgmts.containsKey(tableName)){
							System.out.println("table name not exists, or this operation is not supported on this table");
							return;
						}
						try{							
							Connection con = DatabaseController.connectToDB();
							Statement statement = con.createStatement();
							mgmts.get(tableName).initialize(statement); //recreate
							statement.close();
							con.commit();
						}catch(Exception e){
							e.printStackTrace();
						}
					}
				}
				else if(args[3].equalsIgnoreCase("print")){
					if(args[4].equalsIgnoreCase("database")){						
						System.out.println("be specific, which table? try again.");
					}
					else{	
						if(!mgmts.containsKey(tableName)){
							System.out.println("table name not exists, or this operation is not supported on this table");
							return;
						}
						mgmts.get(tableName).printAll(); //recreate
					}
				}
			}			
			
			  /*Object[] options = {"Yes",
	                  "No"}; 
				int n = JOptionPane.showOptionDialog(null,
				  "Are you sure you want to clear and rebuild the CLR database",
				  "CLR Database Manager",
				  JOptionPane.OK_CANCEL_OPTION,
				  JOptionPane.QUESTION_MESSAGE,
				  null,
				  options,
				  options[1]);

				//System.out.println("n: "+n);
			  
				if(n==0){ //ok
					AnnotationDatabase db = new AnnotationDatabase();
					db.initializeDB();
				}*/
			  
		  }

	   public static ResourceAccess getResourceManagement(){
		   return resourceMgmt;
	   }
	   
	   public static PersonAccess getPersonsManagement(){
		   return personsMgmt;
	   }

	   public static AnnotationAccess getAnnotationManagement(){
		   return annoMgmt;
	   }
	   
	   //old things:

	   /*   public void addTypeAndRateToAnnoTable(Connection con){ //added to AnnotationDatabase.initialize(..)
	   		  
	   	   boolean innerConnection = false;
	   	   
	   	   if(con == null){
	   		   con = DatabaseController.connectToDB();
	   		   innerConnection = true;
	   	   }
	   	   
	   	   	Statement statement = null;
	   	   	
	   	   	if( con == null ) {//in case of exception
	   	   		System.out.println("Failed to connect to DB, none of DB queries will be handled from now. Try again.");	   	
	   	   	}
	   	   	
	   	   	try {			
	   	   		statement = con.createStatement();
	   	   		
	   	   		statement.executeUpdate("ALTER TABLE Annotation " +
	   	   				"ADD COLUMN " +
	   	   					"type VARCHAR(10) " +
	   	   				"ADD COLUMN " +
	   	   					"rateByAnno SMALLINT " + //for notes: 1:pos, 2:neg, 3:neutral, 4:informative, 5:no category
//	   	   				"ADD CONSTRAINT rateValue CHECK (rateByAnno < 6 AND rateByAnno > 0 )" +
	   	   				"");
	   	   		
	   	   		statement.close();
	   		   if(innerConnection){
	   			   con.commit();
	   			   con.close();				   
	   		   }				
	   		
	   		} catch (SQLException ex) {
	   			DB2SqlException.printSqlErrorMsg(ex);
	   		}
	      }
	   */   

	   /*public void createRateAndReadingTables(){
	   
	   Connection con = connectToDB();
	   try{
		   Statement statement = con.createStatement();
		   
		   rateMgmt.initialize(statement);
		   resourceMgmt.getReadingManagement().initialize(statement);
		   
		   statement.close();
		   con.commit();
			
		}catch (SQLException e) {
			System.err.println("SQLException:");
			while(e!=null) {
				System.err.println ("Error msg: " + e.getMessage());
				System.err.println ("SQLSTATE: " + e.getSQLState());
				System.err.println ("Error code: " + e.getErrorCode());
				e.printStackTrace();
				e = e.getNextException();
			}			
		} catch (Exception e) {			
			e.printStackTrace();		
		} 
   }*/
   
	   /*public void addAbstractToBibtexTable(){
	   Connection con = connectToDB();
	   Statement statement = null;
	   	
	   	try {			
	   		statement = con.createStatement();
	   		
	   		statement.executeUpdate("ALTER TABLE BibTex " +
	   				"ADD COLUMN " +
	   					"abstract VARCHAR(5000) "+
	   				"ALTER COLUMN title SET DATA TYPE VARCHAR(300) "
	   				);
	   		
	   		statement.close();
		   
		   con.commit();
		   con.close();				   
		   				
		
		} catch (SQLException ex) {
			DB2SqlException.printSqlErrorMsg(ex);
		}
   }*/
   
/*   public void changeTagsFromClobToVarchar(){ //TODO to support 'LIKE' 
	   Connection con = connectToDB();
	   Statement statement = null;
	   	
	   	try {			
	   		statement = con.createStatement();
	   		
	   		statement.executeUpdate("CALL SYSPROC.ALTOBJ( "+
	   				"'APPLY_CONTINUE_ON_ERROR' ," +
	   				"'CREATE TABLE TAG ( " +
	   					"ID INTEGER GENERATED ALWAYS AS " +
	   						"IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, " +
	   					//"labels VARCHAR(32600), " +
	   					"CONSTRAINT ID_UNIQ PRIMARY KEY(ID) " +
	   				") IN CLRSPACE' " +
	   				", -1, ?)"
	   				);
	   		
	   		statement.close();
		   
		   con.commit();
		   con.close();				   
		   				
		
		} catch (SQLException ex) {
			DB2SqlException.printSqlErrorMsg(ex);			
		}
   }
*/   
   
   /*public void addTextSnapshots(){ //also added to initializeDB
	   
	   Connection con = connectToDB();
	   Statement statement = null;
	   	
	   	try {			
	   		statement = con.createStatement();
	   		
	   		textsnapMgmt.initialize(statement);
	   		
	   		addAnotherSectionForTextsnapshots();
	   		
	   		statement.executeUpdate("ALTER TABLE Annotation " +
	   				"ADD COLUMN " +
	   					"textsnapshot INTEGER " +	   				
	   				"ADD CONSTRAINT FK_textid FOREIGN KEY (textsnapshot) " +
                	"REFERENCES TextSnapshots (ID) ON DELETE CASCADE " +
	   				"");
	   		
	   		statement.close();
		   
		   con.commit();
		   con.close();				   
		   				
		
		} catch (SQLException ex) {
			DB2SqlException.printSqlErrorMsg(ex);			
		}
   }*/
   
   /*public void addAnotherSectionForTextsnapshots(){
	   Connection con = connectToDB();
	   Statement statement = null;
	   	
	   	try {			
	   		statement = con.createStatement();
	   		
//	   		textsnapMgmt.initialize(statement);
	   		
	   		statement.executeUpdate("ALTER TABLE TextSnapshots " +
	   				"ADD COLUMN " +
	   					"previousSection VARCHAR(200) "+
	   				"ALTER COLUMN section SET DATA TYPE VARCHAR(200) ");
	   		
	   		statement.close();
		   
		   con.commit();
		   con.close();				   
		   				
		
		} catch (SQLException ex) {
			DB2SqlException.printSqlErrorMsg(ex);			
		}
   }*/

}
